﻿using Oracle.ManagedDataAccess.Client;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.IO;
using System.Threading.Tasks;

namespace Dapper.Sharding
{
    internal class OracleClient : IClient
    {

        public OracleClient(DataBaseConfig config) : base(DataBaseType.Oracle, config)
        {
            if (config.Database_Size_Mb <= 0)
            {
                config.Database_Size_Mb = 1;
            }
            if (config.Database_SizeGrowth_Mb <= 0)
            {
                config.Database_SizeGrowth_Mb = 1;
            }
            ConnectionString = ConnectionStringBuilder.BuilderOracleSysdba(config);
        }

        public override string ConnectionString { get; set; }


        #region protected method

        protected override IDatabase CreateIDatabase(string name)
        {
            return new OracleDatabase(name, this);
        }

        #endregion

        public override string GetDatabaseScript(string name, string tablespace = null, bool useGis = false, string ext = null)
        {
            throw new NotImplementedException();
        }

        public override void CreateDatabase(string name, string tablespace = null, bool useGis = false, string ext = null)
        {
            var upName = name.ToUpper();
            string dbpath;
            if (!string.IsNullOrEmpty(Config.Database_Path))
            {
                dbpath = Path.Combine(Config.Database_Path, upName + ".DBF");
            }
            else
            {
                dbpath = upName + ".DBF";
            }

            string pwd;
            if (string.IsNullOrEmpty(Config.Password))
            {
                pwd = upName;
            }
            else
            {
                pwd = Config.Password;
            }

            var sql1 = $"create user {upName} identified by \"{pwd}\"";
            var sql2 = $"grant create session,create table,unlimited tablespace to {upName}";
            var sql3 = $"alter user {upName} account unlock";
            var sql4 = $"grant connect,resource,dba to {upName}";
            var sql5 = $"create tablespace {upName} datafile '{dbpath}' size {Config.Database_Size_Mb}m autoextend on next {Config.Database_SizeGrowth_Mb}m";
            var sql6 = $"alter user {upName} default tablespace {upName}";

            using (var conn = GetConn())
            {
                using (var tran = conn.BeginTransaction())
                {
                    try
                    {
                        conn.Execute(sql1, null, tran);
                        conn.Execute(sql2, null, tran);
                        conn.Execute(sql3, null, tran);
                        conn.Execute(sql4, null, tran);
                        conn.Execute(sql5, null, tran);
                        conn.Execute(sql6, null, tran);
                        tran.Commit();
                    }
                    catch
                    {
                        tran.Rollback();
                        throw;
                    }

                }

            }
        }

		public override async Task CreateDatabaseAsync(string name, string tablespace = null, bool useGis = false, string ext = null)
		{
			var upName = name.ToUpper();
			string dbpath;
			if (!string.IsNullOrEmpty(Config.Database_Path))
			{
				dbpath = Path.Combine(Config.Database_Path, upName + ".DBF");
			}
			else
			{
				dbpath = upName + ".DBF";
			}

			string pwd;
			if (string.IsNullOrEmpty(Config.Password))
			{
				pwd = upName;
			}
			else
			{
				pwd = Config.Password;
			}

			var sql1 = $"create user {upName} identified by \"{pwd}\"";
			var sql2 = $"grant create session,create table,unlimited tablespace to {upName}";
			var sql3 = $"alter user {upName} account unlock";
			var sql4 = $"grant connect,resource,dba to {upName}";
			var sql5 = $"create tablespace {upName} datafile '{dbpath}' size {Config.Database_Size_Mb}m autoextend on next {Config.Database_SizeGrowth_Mb}m";
			var sql6 = $"alter user {upName} default tablespace {upName}";

			using var conn = await GetConnAsync();
			using var tran = conn.BeginTransaction();
			try
			{
				await conn.ExecuteAsync(sql1, null, tran);
				await conn.ExecuteAsync(sql2, null, tran);
				await conn.ExecuteAsync(sql3, null, tran);
				await conn.ExecuteAsync(sql4, null, tran);
				await conn.ExecuteAsync(sql5, null, tran);
				await conn.ExecuteAsync(sql6, null, tran);
				tran.Commit();
			}
			catch
			{
				tran.Rollback();
				throw;
			}
		}

		public override void DropDatabase(string name)
        {
            var sql = $@"DROP USER {name.ToUpper()} CASCADE";
            var sql2 = $"DROP TABLESPACE {name.ToUpper()} INCLUDING CONTENTS AND DATAFILES";
            Execute(sql);
            Execute(sql2);
            DataBaseCache.TryRemove(name, out _);
        }

        public override bool ExistsDatabase(string name)
        {
            return ExecuteScalar<long>($"SELECT COUNT(1) FROM dba_users WHERE USERNAME='{name.ToUpper()}'") > 0;
        }

		public override async Task<bool> ExistsDatabaseAsync(string name)
		{
			return await ExecuteScalarAsync<long>($"SELECT COUNT(1) FROM dba_users WHERE USERNAME='{name.ToUpper()}'") > 0;
		}

		public override DbConnection GetConn()
        {
            var conn = new OracleConnection(ConnectionString);
            if (conn.State == ConnectionState.Closed)
            {
                try
                {
                    conn.Open();
                }
                catch
                {
                    conn.Dispose();
                    throw;
                }
            }
            return conn;
        }

        public override async Task<DbConnection> GetConnAsync()
        {
            var conn = new OracleConnection(ConnectionString);
            if (conn.State == ConnectionState.Closed)
            {
                try
                {
                    await conn.OpenAsync();
                }
                catch
                {
#if CORE
                    await conn.DisposeAsync();
#else
                    conn.Dispose();
#endif
                    throw;
                }
            }
            return conn;
        }

        public override List<string> ShowDatabases()
        {
            return Query<string>("SELECT USERNAME FROM dba_users");
        }

        public override List<string> ShowDatabasesExcludeSystem()
        {
            return Query<string>("SELECT USERNAME FROM dba_users WHERE DEFAULT_TABLESPACE NOT IN('SYSTEM','SYSAUX','USERS')");
        }

        public override void Vacuum(string dbname)
        {
            throw new NotImplementedException();
        }

		protected override string GetDbName(string connStr)
		{
			using var conn = new OracleConnection(connStr);
			return conn.Database;
		}

        public override void CreateTablespace(string name, string dir)
        {
            throw new NotImplementedException();
        }

        public override Task CreateTablespaceAsync(string name, string dir)
        {
            throw new NotImplementedException();
        }

        public override void DropTablespace(string name)
        {
            throw new NotImplementedException();
        }

        public override Task DropTablespaceAsync(string name)
        {
            throw new NotImplementedException();
        }

        public override bool ExistsTablespace(string name)
        {
            throw new NotImplementedException();
        }

        public override Task<bool> ExistsTablespaceAsync(string name)
        {
            throw new NotImplementedException();
        }

        public override void SetTablespace(string database, string tablespace)
        {
            throw new NotImplementedException();
        }

        public override Task SetTablespaceAsync(string database, string tablespace)
        {
            throw new NotImplementedException();
        }

        public override void CloseSession(string database = null, bool closePostgres = false)
        {
            throw new NotImplementedException();
        }

        public override Task CloseSessionAsync(string database = null, bool closePostgres = false)
        {
            throw new NotImplementedException();
        }
    }
}
